package com.android.canvasproject.model.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.android.canvasproject.model.entity.Attachment;
import com.android.canvasproject.model.entity.Canvas;
import com.android.canvasproject.model.entity.Comment;
import com.android.canvasproject.model.entity.Person;
import com.android.canvasproject.model.entity.Postit;
import com.android.canvasproject.model.entity.TaskList;
import com.android.canvasproject.model.entity.User;
import com.android.canvasproject.model.interfaces.ProviderInterface;
import com.android.canvasproject.view.utils.ConstantsAction;

public class ProviderDaoOpenHelper implements ProviderInterface {

	public static final String DATABASE_NAME = "canvas_db";
	public static final int DATABASE_VERSION = 1;
	private static final String CLASSNAME = ProviderDaoOpenHelper.class.getSimpleName();

	private SQLiteDatabase db;
	private final DBOpenHelper dbOpenHelper;

	public ProviderDaoOpenHelper(Context context) {
		dbOpenHelper = new DBOpenHelper(context, DATABASE_NAME, DATABASE_VERSION);
		establishDB();
	}

	private void establishDB() {
		if (db == null)
			db = dbOpenHelper.getWritableDatabase();
	}

	public void cleanup() {
		if (db != null) {
			db.close();
			db = null;
		}
	}

	@Override
	public Integer save(String table, ContentValues values) throws SQLException {
		long result = -1;

		try {
			result = db.insert(table, null, values);

		} catch (SQLException e) {
			Log.e(table, ConstantsAction.Database.INSERT, e);
		}

		Log.d(table, ConstantsAction.Database.INSERT);
		return Integer.valueOf(String.valueOf(result));
	}

	@Override
	public int update(String table, ContentValues values, String whereClause, String[] args) throws SQLException {
		int result = -1;

		try {
			result = db.update(table, values, "1=1 AND " + whereClause, args);

		} catch (SQLException e) {
			Log.e(table, ConstantsAction.Database.UPDATE, e);
		}

		Log.d(table, ConstantsAction.Database.UPDATE);
		return result;
	}

	@Override
	public int delete(String table, String whereClause, String[] whereArgs) throws SQLException {
		int result = -1;

		try {
			result = db.delete(table, whereClause, whereArgs);

		} catch (SQLException e) {
			Log.e(table, ConstantsAction.Database.DELETE, e);
		}

		Log.d(table, ConstantsAction.Database.DELETE);
		return result;
	}

	@Override
	public Map<String, Object> findById(String table, String[] cols, long id) throws SQLException {
		Map<String, Object> map = new HashMap<String, Object>();
		Cursor cursor = null;

		try{
			cursor = db.query(true, table, cols, "_id = " + id, null, null, null, null, null);
			if(cursor.getCount() > 0) {
				cursor.moveToFirst();

				for(int i = 0; i < cursor.getColumnCount(); i++){
					Object o = new Object();
	
					if(cursor.getType(i) == Cursor.FIELD_TYPE_BLOB)
						o = cursor.getBlob(i);
	
					else if (cursor.getType(i) == Cursor.FIELD_TYPE_FLOAT)
						o = cursor.getFloat(i);
	
					else if(cursor.getType(i) == Cursor.FIELD_TYPE_INTEGER)
						o = cursor.getInt(i);
	
					else if(cursor.getType(i) == Cursor.FIELD_TYPE_NULL)
						o = cursor.getString(i);
	
					else if(cursor.getType(i) == Cursor.FIELD_TYPE_STRING)
						o = cursor.getString(i);
	
					map.put(cursor.getColumnName(i), o);
				}
			}
		} catch (SQLException e) {
			Log.e(table, ConstantsAction.Database.QUERY, e);

		} finally {
			if(cursor != null && !cursor.isClosed())
				cursor.close();
		}

		Log.d(table, ConstantsAction.Database.QUERY);
		return map;
	}
	
	@Override
	public List<Map<String, Object>> findAll(String table, String[] cols) throws SQLException {
		List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
		Cursor cursor = null;

		try {
			String orderBy = "creation_date DESC";
			cursor = db.query(table, cols, null, null, null, null, orderBy);
			int numRows = cursor.getCount();
			cursor.moveToFirst();
			
			for(int i = 0; i < numRows; i++){
				Map<String, Object> objectMap = new HashMap<String, Object>();
	
				for(int j = 0; j < cursor.getColumnCount(); j++){
					Object o = new Object();
	
					if(cursor.getType(j) == Cursor.FIELD_TYPE_BLOB)
						o = cursor.getBlob(j);
	
					else if (cursor.getType(j) == Cursor.FIELD_TYPE_FLOAT)
						o = cursor.getFloat(j);
	
					else if(cursor.getType(j) == Cursor.FIELD_TYPE_INTEGER)
						o = cursor.getInt(j);
	
					else if(cursor.getType(j) == Cursor.FIELD_TYPE_NULL)
						o = cursor.getString(j);
	
					else if(cursor.getType(j) == Cursor.FIELD_TYPE_STRING)
						o = cursor.getString(j);

					objectMap.put(cursor.getColumnName(j), o);
				}

				resultList.add(objectMap);
				cursor.moveToNext();
			}
		} catch (SQLException e) {
			Log.e(table, ConstantsAction.Database.QUERY, e);

		} finally {
			if(cursor != null && !cursor.isClosed())
				cursor.close();
		}
		
		Log.d(table, ConstantsAction.Database.QUERY);
		return resultList;
	}
	
	@Override
	public List<Map<String, Object>> findFilter(String table, String[] cols, String whereClause) throws SQLException {
		List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
		Cursor cursor = null;

		try {
			String orderBy = "creation_date DESC";
			cursor = db.query(table, cols, "1=1 AND " + whereClause, null, null, null, orderBy);
			int numRows = cursor.getCount();
			cursor.moveToFirst();

			for(int i = 0; i < numRows; i++){
				Map<String, Object> objectMap = new HashMap<String, Object>();
	
				for(int j = 0; j < cursor.getColumnCount(); j++){
					Object o = new Object();
	
					if(cursor.getType(j) == Cursor.FIELD_TYPE_BLOB)
						o = cursor.getBlob(j);
	
					else if (cursor.getType(j) == Cursor.FIELD_TYPE_FLOAT)
						o = cursor.getFloat(j);
	
					else if(cursor.getType(j) == Cursor.FIELD_TYPE_INTEGER)
						o = cursor.getInt(j);
	
					else if(cursor.getType(j) == Cursor.FIELD_TYPE_NULL)
						o = cursor.getString(j);
	
					else if(cursor.getType(j) == Cursor.FIELD_TYPE_STRING)
						o = cursor.getString(j);

					objectMap.put(cursor.getColumnName(j), o);
				}

				resultList.add(objectMap);
				cursor.moveToNext();
			}
		} catch (SQLException e) {
			Log.e(table, ConstantsAction.Database.QUERY, e);

		} finally {
			if(cursor != null && !cursor.isClosed())
				cursor.close();
		}
		
		Log.d(table, ConstantsAction.Database.QUERY);
		return resultList;
	}
	
	private static class DBOpenHelper extends SQLiteOpenHelper {
		public DBOpenHelper(Context context, String dbName, int dbVersion) {
			super(context, dbName, null, dbVersion);
			Log.d(CLASSNAME, ConstantsAction.Database.CREATED_OPEN_DATABASE);
		}

		@Override
		public void onOpen(SQLiteDatabase db) {
			super.onOpen(db);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			try {
				//CREATE TABLE
				db.execSQL(Person.PersonDao.DB_CREATE_PERSON);
				db.execSQL(User.UserDao.DB_CREATE_USER);
				db.execSQL(Canvas.CanvasDao.DB_CREATE_CANVAS);
				db.execSQL(Postit.PostitDao.DB_CREATE_POSTIT);
				db.execSQL(Comment.CommentDao.DB_CREATE_COMMENT);
				db.execSQL(TaskList.TaskListDao.DB_CREATE_TASKLIST);
				db.execSQL(Attachment.AttachmentDao.DB_CREATE_ATTACHMENT);
				db.execSQL(Canvas.CanvasDao.DB_CREATE_CONTRIBUTORS);

				db.execSQL("INSERT OR IGNORE INTO PERSON (name, occupation, city, state, academic_training, teaching_institution, enterprise, starting_last_job, ending_last_job, location_last_job, description_last_activity, profile_photo) VALUES ('Diogo Medeiros', 'Analista Desenvolvedor de Sistemas', 'João Pessoa', 'Paraíba', 'Análise e Desenvolvimento de Sistemas', 'Faculdade Estácio iDez', 'Dynavideo', '2013-01-01', '2013-05-05', 'Dynavideo', 'Desenvolvimento do Sistema do Protocolo', null);");

				Log.d(ProviderDaoOpenHelper.CLASSNAME, ConstantsAction.Database.CREATED_TABLE);

			} catch (SQLiteException e) {
				Log.e(ConstantsAction.Database.CREATED_TABLE, ProviderDaoOpenHelper.CLASSNAME, e);
			}
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO ANALISAR UPGRADE DO BANCO DE DADOS
		}
	}
}